﻿//Please add references

using System;
using System.Data;
using System.Data.SqlClient;
using YwangHRMSys.DBUtility;
using YwangHRMSys.Model;


namespace YwangHRMSys.DAL
    {
        /// <summary>
        ///     数据访问类:OperatorInfoDAL
        /// </summary>
        public class OperatorInfoDAL
            {
                #region  BasicMethod

                /// <summary>
                ///     软删除操作员信息
                /// </summary>
                /// <param name="id"></param>
                public void DeleteById( Guid id )
                {
                    SqlHelper.ExecuteNonQuery
                            ("UPDATE dbo.OperatorInfo SET IsDeleted=1 WHERE Id=@Id", new SqlParameter("@Id", id));
                }

                public void Locked( Guid id )
                {
                    SqlHelper.ExecuteNonQuery
                            ("UPDATE dbo.OperatorInfo SET IsLocked=5 WHERE Id=@Id", new SqlParameter("@Id", id));
                }

                public void UnLocked( Guid id )
                {
                    SqlHelper.ExecuteNonQuery
                            ("UPDATE dbo.OperatorInfo SET IsLocked=0 WHERE Id=@Id", new SqlParameter("@Id", id));
                }

                /// <summary>
                /// 重置密码
                /// </summary>
                /// <param name="id"></param>
                /// <param name="userPassword"></param>
                public void ResetPassword(Guid id,string userPassword)
                {

                    SqlHelper.ExecuteNonQuery
                           ("UPDATE dbo.OperatorInfo SET PassWord=@PassWord WHERE Id=@Id", new SqlParameter("@Id", id), new SqlParameter("@PassWord", userPassword));
                }


                /// <summary>
                ///     查询所有除开离职操作员数据列表
                /// </summary>
                /// <returns></returns>
                public OperatorInfoModel[] ListAll()
                {
                    DataTable table = SqlHelper.ExecuteDataTable
                            (
                                    "select Id,UserName,PassWord,IsDeleted,RealName,IsLocked,RegisterTime FROM dbo.OperatorInfo where IsDeleted=0  ");
                    var operators = new OperatorInfoModel[table.Rows.Count];
                    for (int i = 0; i < table.Rows.Count; i++)
                        {
                            operators[i] = Toperator(table.Rows[i]);

                        }
                    return operators;
                }


                /// <summary>
                ///     根据ID来查询操作员数据
                /// </summary>
                /// <param name="Id"></param>
                /// <returns></returns>
                public OperatorInfoModel GetById( Guid Id )
                {
                    DataTable table = SqlHelper.ExecuteDataTable
                            (
                                    "SELECT * FROM dbo.OperatorInfo WHERE Id=@Id",
                                    new SqlParameter
                                            (
                                            "@Id",
                                            Id));

                    if (table.Rows.Count <= 0)
                        {
                            return null;
                        }
                    if (table.Rows.Count > 1)
                        {
                            throw new Exception("存在ID重复!");
                        }
                    //将第一行的数据传入到Toperator中
                    return Toperator(table.Rows[0]);
                }


                /// <summary>
                ///     数据库中的值传输到model内中
                /// </summary>
                /// <param name="row"></param>
                /// <returns></returns>
                private static OperatorInfoModel Toperator( DataRow row )
                {
                    var op = new OperatorInfoModel();
                    op.Id = (Guid) row["Id"];
                    op.UserName = (string) row["UserName"];
                    op.PassWord = (string) row["PassWord"];
                    op.IsDeleted = (bool) row["IsDeleted"];
                    op.IsLocked = (int) row["IsLocked"];
                    op.RealName = (string) row["RealName"];
                    op.RegisterTime = (DateTime) row["RegisterTime"];
                    return op;
                }

                /// <summary>
                ///     密码错误锁定操作人员次数+1
                /// </summary>
                /// <param name="id"></param>
                public void UpdateLocked( Guid id )
                {
                    SqlHelper.ExecuteNonQuery
                            (
                                    "UPDATE dbo.OperatorInfo SET IsLocked=IsLocked+1 WHERE Id=@Id",
                                    new SqlParameter("@Id", id));
                }

                /// <summary>
                ///     登录成功，密码错误次数清零。
                /// </summary>
                /// <param name="id"></param>
                public void UpdateUnLocked( Guid id )
                {
                    SqlHelper.ExecuteNonQuery
                            ("UPDATE dbo.OperatorInfo SET IsLocked=0 WHERE Id=@Id", new SqlParameter("@Id", id));
                }

                /// <summary>
                ///     根据用户名查询所有不含离职员工数据，
                /// </summary>
                /// <param name="userName"></param>
                /// <returns></returns>
                public OperatorInfoModel GetByUserName( string userName )
                {
                    DataTable table = SqlHelper.ExecuteDataTable
                            (
                                    "SELECT * FROM dbo.OperatorInfo WHERE UserName=@UserName AND IsDeleted=0 AND IsLocked<5",
                                    new SqlParameter
                                            (
                                            "@UserName",
                                            userName));

                    if (table.Rows.Count <= 0)
                        {
                            return null;
                        }
                    if (table.Rows.Count > 1)
                        {
                            throw new Exception("存在重复用户名");
                        }
                    //将第一行的数据传入到Toperator中
                    return Toperator(table.Rows[0]);
                }

                /// <summary>
                ///     新增操作员信息
                /// </summary>
                /// <param name="model"></param>
                public void InsertOperatorInfo( OperatorInfoModel model )
                {
                    //bit类型中，SQL语句中要写0、1
                    //在.NET要用bool表示

                    SqlHelper.ExecuteNonQuery
                            (
                                    "INSERT INTO dbo.OperatorInfo(Id, UserName ,PassWord ,IsDeleted ,RealName ,IsLocked )VALUES  (@Id,@UserName ,@PassWord,0  ,@RealName ,0 )",new SqlParameter("@Id",model.Id),
                                    new SqlParameter("@UserName", model.UserName),
                                    new SqlParameter("@PassWord", model.PassWord),
                                    new SqlParameter("@RealName", model.RealName));
                }


                /// <summary>
                ///     更新密码版本
                /// </summary>
                /// <param name="model"></param>
                public void Update( Guid id, string userName, string realName, string password )
                {
                    SqlHelper.ExecuteNonQuery
                            (
                                    "UPDATE dbo.OperatorInfo SET UserName=@UserName,RealName=@RealName,PassWord=@PassWord WHERE Id=@Id",
                                    new SqlParameter
                                            (
                                            "@UserName",
                                            userName),
                                    new SqlParameter
                                            (
                                            "@RealName",
                                            realName),
                                    new SqlParameter
                                            (
                                            "@Id",
                                            id),
                                    new SqlParameter("@PassWord", password));
                }


                /// <summary>
                ///     不用更新密码版本
                /// </summary>
                /// <param name="id"></param>
                /// <param name="userName"></param>
                /// <param name="realName"></param>
                public void Update( Guid id, string userName, string realName )
                {
                    SqlHelper.ExecuteNonQuery
                            (
                                    "UPDATE dbo.OperatorInfo SET UserName=@UserName,RealName=@RealName WHERE Id=@Id",
                                    new SqlParameter
                                            (
                                            "@UserName",
                                            userName),
                                    new SqlParameter("@RealName", realName),
                                    new SqlParameter("@Id", id)
                            );
                }

                #endregion  BasicMethod

                #region  ExtensionMethod

                #endregion  ExtensionMethod
            }
    }